/**
 * @swagger
 * /api/asset/time-analysis:
 *   get:
 *     tags:
 *       - 资产管理
 *     summary: 获取资产时间分析数据
 *     description: 分析资产在不同时间段的使用情况和趋势
 *     responses:
 *       200:
 *         description: 成功获取时间分析数据
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 code:
 *                   type: integer
 *                   example: 200
 *                   description: 响应状态码
 *                 message:
 *                   type: string
 *                   example: "时间分析获取成功"
 *                   description: 响应消息
 *                 data:
 *                   type: object
 *                   properties:
 *                     overview:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 时间分析概览标题
 *                         statistics:
 *                           type: object
 *                           properties:
 *                             totalAssets:
 *                               type: integer
 *                               description: 总资产数
 *                             assetsWithDate:
 *                               type: integer
 *                               description: 有购买日期的资产数
 *                             dateRatio:
 *                               type: string
 *                               description: 日期完整率(%)
 *                             earliestDate:
 *                               type: string
 *                               description: 最早购买日期
 *                             latestDate:
 *                               type: string
 *                               description: 最晚购买日期
 *                             timeSpan:
 *                               type: string
 *                               description: 时间跨度(年)
 *                     yearlyStatistics:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 年度统计标题
 *                         years:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               year:
 *                                 type: string
 *                                 description: 年份
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               totalFiscalFunding:
 *                                 type: string
 *                                 description: 财政拨款总额
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               totalBuildingArea:
 *                                 type: string
 *                                 description: 总建筑面积
 *                               totalLandArea:
 *                                 type: string
 *                                 description: 总土地面积
 *                               uniqueKeepers:
 *                                 type: integer
 *                                 description: 唯一领用人数
 *                               percentage:
 *                                 type: string
 *                                 description: 占比(%)
 *                     quarterlyStatistics:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 季度统计标题
 *                         quarters:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               quarter:
 *                                 type: string
 *                                 description: 季度(Q1-Q4)
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               percentage:
 *                                 type: string
 *                                 description: 占比(%)
 *                     monthlyStatistics:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 月度统计标题
 *                         months:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               month:
 *                                 type: string
 *                                 description: 月份(1-12)
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               percentage:
 *                                 type: string
 *                                 description: 占比(%)
 *                     timeValueAnalysis:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 时间价值分析标题
 *                         valueRanges:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               valueRange:
 *                                 type: string
 *                                 description: 价值区间
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               avgPurchaseYear:
 *                                 type: string
 *                                 description: 平均购买年份
 *                               recentAssets:
 *                                 type: integer
 *                                 description: 近期资产数(2020年后)
 *                               recentRatio:
 *                                 type: string
 *                                 description: 近期资产比例(%)
 *                     timeCategoryAnalysis:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 时间分类分析标题
 *                         categories:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               categoryName:
 *                                 type: string
 *                                 description: 分类名称
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               avgPurchaseYear:
 *                                 type: string
 *                                 description: 平均购买年份
 *                               recentAssets:
 *                                 type: integer
 *                                 description: 近期资产数(2020年后)
 *                               recentRatio:
 *                                 type: string
 *                                 description: 近期资产比例(%)
 *                     ageAnalysis:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 资产年龄分析标题
 *                         ageGroups:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               ageGroup:
 *                                 type: string
 *                                 description: 年龄组
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               percentage:
 *                                 type: string
 *                                 description: 占比(%)
 *                     timeCompleteness:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 时间数据完整性分析标题
 *                         completeness:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               timeRange:
 *                                 type: string
 *                                 description: 时间范围
 *                               totalAssets:
 *                                 type: integer
 *                                 description: 总资产数
 *                               assetsWithDate:
 *                                 type: integer
 *                                 description: 有日期的资产数
 *                               dateRatio:
 *                                 type: string
 *                                 description: 日期完整率(%)
 *                               hasKeeper:
 *                                 type: integer
 *                                 description: 有领用人的资产数
 *                               hasPrice:
 *                                 type: integer
 *                                 description: 有价格的资产数
 *                               keeperRatio:
 *                                 type: string
 *                                 description: 领用人完整率(%)
 *                               priceRatio:
 *                                 type: string
 *                                 description: 价格完整率(%)
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *       500:
 *         description: 服务器内部错误
 */
import { withCors } from '../../../lib/cors';
import { success, error as errorResponse, handleResponse } from '../../../lib/response';
import { query } from '../../../lib/db';

/**
 * 处理资产时间分析请求
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 */
async function handler(req, res) {
  try {
    
    // 购买时间基础统计
    const timeBasicQuery = `
      SELECT 
        COUNT(*) as totalAssets,
        COUNT(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN 1 END) as assetsWithDate,
        MIN(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN PURCHASE_DATE END) as earliestDate,
        MAX(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN PURCHASE_DATE END) as latestDate
      FROM wd_sec_assets12
    `;

    const timeBasicResult = await query(timeBasicQuery);
    const timeBasicData = timeBasicResult[0];

    // 按年份统计
    const yearlyStatsQuery = `
      SELECT 
        SUBSTRING(PURCHASE_DATE, 1, 4) as purchaseYear,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue
      FROM wd_sec_assets12
      WHERE PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != ''
      GROUP BY SUBSTRING(PURCHASE_DATE, 1, 4)
      ORDER BY purchaseYear DESC
      LIMIT 15
    `;

    const yearlyResult = await query(yearlyStatsQuery);

    // 按月份统计（近几年的月份分布）
    const monthlyStatsQuery = `
      SELECT 
        SUBSTRING(PURCHASE_DATE, 6, 2) as purchaseMonth,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue
      FROM wd_sec_assets12
      WHERE PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' 
        AND SUBSTRING(PURCHASE_DATE, 1, 4) >= '2020'
      GROUP BY SUBSTRING(PURCHASE_DATE, 6, 2)
      ORDER BY purchaseMonth
    `;

    const monthlyResult = await query(monthlyStatsQuery);

    // 时间段分析
    const periodAnalysisQuery = `
      SELECT 
        CASE 
          WHEN PURCHASE_DATE IS NULL OR PURCHASE_DATE = '' THEN '无购买日期'
          WHEN SUBSTRING(PURCHASE_DATE, 1, 4) < '2010' THEN '2010年前'
          WHEN SUBSTRING(PURCHASE_DATE, 1, 4) BETWEEN '2010' AND '2015' THEN '2010-2015年'
          WHEN SUBSTRING(PURCHASE_DATE, 1, 4) BETWEEN '2016' AND '2020' THEN '2016-2020年'
          WHEN SUBSTRING(PURCHASE_DATE, 1, 4) > '2020' THEN '2021年后'
        END as timePeriod,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue
      FROM wd_sec_assets12
      GROUP BY 
        CASE 
          WHEN PURCHASE_DATE IS NULL OR PURCHASE_DATE = '' THEN '无购买日期'
          WHEN SUBSTRING(PURCHASE_DATE, 1, 4) < '2010' THEN '2010年前'
          WHEN SUBSTRING(PURCHASE_DATE, 1, 4) BETWEEN '2010' AND '2015' THEN '2010-2015年'
          WHEN SUBSTRING(PURCHASE_DATE, 1, 4) BETWEEN '2016' AND '2020' THEN '2016-2020年'
          WHEN SUBSTRING(PURCHASE_DATE, 1, 4) > '2020' THEN '2021年后'
        END
      ORDER BY 
        CASE 
          WHEN timePeriod = '无购买日期' THEN 0
          WHEN timePeriod = '2010年前' THEN 1
          WHEN timePeriod = '2010-2015年' THEN 2
          WHEN timePeriod = '2016-2020年' THEN 3
          ELSE 4
        END
    `;

    const periodResult = await query(periodAnalysisQuery);

    // 各分类的时间分布
    const categoryTimeQuery = `
      SELECT 
        COALESCE(ASSETS_SORT_GGJ_NAME, '未分类') as categoryName,
        COUNT(*) as totalAssets,
        COUNT(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN 1 END) as assetsWithDate,
        MIN(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN SUBSTRING(PURCHASE_DATE, 1, 4) END) as earliestYear,
        MAX(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN SUBSTRING(PURCHASE_DATE, 1, 4) END) as latestYear,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      GROUP BY COALESCE(ASSETS_SORT_GGJ_NAME, '未分类')
      HAVING totalAssets > 0
      ORDER BY totalAssets DESC
      LIMIT 10
    `;

    const categoryTimeResult = await query(categoryTimeQuery);

    // 最近购买的资产
    const recentPurchaseQuery = `
      SELECT 
        PURCHASE_DATE,
        COALESCE(ASSETS_SORT_GGJ_NAME, '未分类') as categoryName,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue
      FROM wd_sec_assets12
      WHERE PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != ''
      GROUP BY PURCHASE_DATE, COALESCE(ASSETS_SORT_GGJ_NAME, '未分类')
      ORDER BY PURCHASE_DATE DESC
      LIMIT 20
    `;

    const recentResult = await query(recentPurchaseQuery);

    const responseData = {
      overview: {
        title: "购买时间统计概览",
        statistics: {
          totalAssets: parseInt(timeBasicData.totalAssets),
          assetsWithDate: parseInt(timeBasicData.assetsWithDate || 0),
          dateRatio: ((parseInt(timeBasicData.assetsWithDate || 0) / parseInt(timeBasicData.totalAssets)) * 100).toFixed(2),
          earliestDate: timeBasicData.earliestDate || '未知',
          latestDate: timeBasicData.latestDate || '未知'
        }
      },
      yearlyTrends: {
        title: "年度购买趋势",
        years: yearlyResult.map(row => ({
          purchaseYear: row.purchaseYear,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          totalFiscalFunding: parseFloat(row.totalFiscalFunding || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2)
        }))
      },
      monthlyPatterns: {
        title: "月度购买模式(2020年后)",
        months: monthlyResult.map(row => ({
          purchaseMonth: row.purchaseMonth,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2)
        }))
      },
      periodAnalysis: {
        title: "时间段分析",
        periods: periodResult.map(row => ({
          timePeriod: row.timePeriod,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          totalFiscalFunding: parseFloat(row.totalFiscalFunding || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2),
          percentage: ((parseInt(row.assetCount) / parseInt(timeBasicData.totalAssets)) * 100).toFixed(2)
        }))
      },
      categoryTimeDistribution: {
        title: "分类时间分布(Top10)",
        categories: categoryTimeResult.map(row => ({
          categoryName: row.categoryName,
          totalAssets: parseInt(row.totalAssets),
          assetsWithDate: parseInt(row.assetsWithDate || 0),
          dateRatio: ((parseInt(row.assetsWithDate || 0) / parseInt(row.totalAssets)) * 100).toFixed(2),
          earliestYear: row.earliestYear || '未知',
          latestYear: row.latestYear || '未知',
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          timeSpan: row.earliestYear && row.latestYear ? 
            (parseInt(row.latestYear) - parseInt(row.earliestYear)).toString() + '年' : '未知'
        }))
      },
      recentPurchases: {
        title: "最近购买记录(Top20)",
        purchases: recentResult.map(row => ({
          purchaseDate: row.PURCHASE_DATE,
          categoryName: row.categoryName,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2)
        }))
      }
    };

    return handleResponse(res, success(responseData, "资产时间分析获取成功"));
    
  } catch (error) {
    console.error('Asset time analysis error:', error);
    return handleResponse(res, errorResponse("获取资产时间分析失败", 500));
  }
}

export default withCors(handler, ['GET']);